package com.ocom.accesservice.dao;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.ocom.accesservice.vo.ParaSumVo;
import com.ocom.accesservice.vo.devicelogin.GetYkmPersonnelListVo;
import com.ocom.accesservice.vo.devicelogin.YkmSumListRVo;
import com.ocom.common.entity.access.EntityPersonnelYkm;
import com.ocom.common.enums.DataSourceEnum;
import com.ocom.common.resp.Result;
import com.ocom.mysql.aspect.DataSourceAspect;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface IDaoPersonnelYkm extends BaseMapper<EntityPersonnelYkm> {

    /**
     * 防疫人员列表（getYkmPersonnelList）
     * */

    @Select({"<script>",
            "select i.com_id,i.dept_id,i.p_id,i.p_name,i.p_code,i.p_sex,d.dept_name "+
            ",y.code_color,y.color_time,y.hs_result,y.hs_time,y.ym_num,y.ym_time,y.update_time "+
             "from  personnel_info i \n" +
            "left join personnel_ykm y on i.com_id=y.com_id and  i.p_id=y.p_id\n" +
            "left join dept_info d ON d.com_id=i.com_id and d.dept_id = i.dept_id and i.p_id=y.p_id \n"+
            "where i.com_id=#{comId} and i.status=0 \n"+
            " <when test=\"pName!=null and pName!=''\"> "+
            " AND i.p_name like concat('%', #{pName}, '%') "+
            " </when> "+
            " <when test=\"pCode!=null and pCode!=''\" > "+
            " AND i.p_code = #{pCode} "+
            " </when> "+
            " <when test=\"deptIdsList!=null and deptIdsList.size() &gt; 0 \" > "+
            " AND i.dept_id  in " +
            " <foreach collection='deptIdsList' item='id' open='(' separator=',' close=')'> "+
            " #{id} "+
            " </foreach> "+
            " </when> "+
             " <when test=\"codeColor==1 and codeColor != ''\" > " +
             " AND y.code_color='红' " +
             " </when> " +
             " <when test=\"codeColor==2  and codeColor != ''\" > " +
             " AND  y.code_color='黄' " +
             " </when> " +
              " <when test=\"codeColor==3  and codeColor != ''\" > " +
                " AND y.code_color='绿' " +
              " </when> " +
              " <when test=\"codeColor==0 and codeColor != ''\" > " +
              " AND y.code_color is null or y.code_color not in ('红','黄','绿') " +
               " </when> " +
               " <when test=\"hsResult==1 and hsResult != ''\" > " +
                " AND y.hs_result='阳性' " +
                " </when> " +
                " <when test=\"hsResult==21 and hsResult != ''\" > " +
                " AND y.hs_result='阴性' and (TIMESTAMPDIFF(HOUR,hs_time,DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%S'))  &lt;= 24)" +
                " </when> " +
                " <when test=\"hsResult==22 and hsResult != ''\" > " +
                " AND y.hs_result='阴性' and (TIMESTAMPDIFF(HOUR,hs_time,DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%S'))  between 24+1 and 24*2) " +
                " </when> " +
                " <when test=\"hsResult==23 and hsResult != ''\" > " +
                " AND y.hs_result='阴性' and (TIMESTAMPDIFF(HOUR,hs_time,DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%S'))  between 24*2+1 and 24*3) " +
                " </when> " +
                " <when test=\"hsResult==24 and hsResult != ''\" > " +
                " AND y.hs_result='阴性' and (TIMESTAMPDIFF(HOUR,hs_time,DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%S'))  between 24*3+1 and 24*7)" +
                " </when> " +
                " <when test=\"hsResult==25 and hsResult != ''\" > " +
                " AND y.hs_result='阴性' and (TIMESTAMPDIFF(HOUR,hs_time,DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%S'))  &gt; 24*7) " +
                " </when> " +
                    " <when test=\"hsResult==0 and hsResult != ''\" > " +
                    " AND y.hs_result is null or y.hs_result not in ('阳性','阴性')" +
                " </when> " +
                " order by locate(y.code_color,'绿,黄,红') desc, locate(y.hs_result,'阳性,阴性') asc",
            "</script>"}
    )
    public List<GetYkmPersonnelListVo> GetYkmPersonnelList(Long comId,List<Integer> deptIdsList,String pCode,
                                                           String pName,Integer codeColor,Integer hsResult);


    @Select({"<script>",
            "select i.com_id,i.dept_id\n" +
                    ",i.p_id,i.p_name,i.p_code,i.p_sex,CONCAT(LEFT(i.p_identity,2),'********',RIGHT(i.p_identity,3)) as identity," +
                    "CONCAT(LEFT(i.p_phone,2),'********',RIGHT(i.p_phone,3)) as phone,i.p_photo_url,d.dept_name\n" +
                    ",y.code_color,y.color_time,y.hs_result,y.hs_time,y.ym_num,y.ym_time,y.update_time\n" +
                    "from  personnel_info i\n" +
                    "left join personnel_ykm y on i.com_id=y.com_id and  i.p_id=y.p_id\n" +
                    "left join dept_info d ON d.com_id=i.com_id and d.dept_id = i.dept_id and i.p_id=y.p_id \n"+
                    "where i.com_id=#{comId}  and i.status=0  "+
                    " <when test=\"pName!=null and pName!=''\"> "+
                    " AND i.p_name like concat('%', #{pName}, '%') "+
                    " </when> "+
                    " <when test=\"pCode!=null and pCode!=''\" > "+
                    " AND i.p_code = #{pCode} "+
                    " </when> "+
                    " <when test=\"deptIdsList!=null and deptIdsList.size() &gt; 0 \" > "+
                    " AND i.dept_id  in " +
                    " <foreach collection='deptIdsList' item='id' open='(' separator=',' close=')'> "+
                    " #{id} "+
                    " </foreach> "+

                    " </when> "+
                    " <when test=\"pullFailed=='pullFailed' and pullFailed!='' or other=='other' and other!=''\" > "+
                    " and (y.p_id is null or y.code_color not in ('红','黄','绿') )"+
                    " </when> "+
                    " <when test=\"red=='red' and red!=''\" > "+
                    " AND y.code_color='红' "+
                    " </when> "+
                    " <when test=\"yellow=='yellow' and yellow!=''\" > "+
                    " AND y.code_color='黄' "+
                    " </when> "+
                    " <when test=\"green=='green' and green!=''\" > "+
                    " AND y.code_color='绿' "+
                    " </when> "+
                    " <when test=\"positive=='positive' and positive!=''\" > "+
                    " AND y.hs_result='阳性' "+
                    " </when> "+
                    " <when test=\"negative=='negative' and negative!=''\" > "+
                    " AND y.hs_result='阴性' "+
                    " </when> "+
                    " <when test=\"noNucleic=='noNucleic' and noNucleic!=''\" > "+
                    " AND y.hs_result  not in ('阴性','阳性')  "+
                    " </when> "+
                    " <when test=\"noNucleic=='noNucleic' and noNucleic!=''\" > "+
                    " AND y.hs_result  not in ('阴性','阳性')  "+
                    " </when> "+
                    " <when test=\"hours24=='hours24' and hours24!=''\" > "+
                    " AND y.hs_result='阴性' and (TIMESTAMPDIFF(HOUR,hs_time,DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%S'))  &lt;=24)  "+
                    " </when> "+
                    " <when test=\"hours48=='hours48' and hours48!=''\" > "+
                    " AND y.hs_result='阴性' and (TIMESTAMPDIFF(HOUR,hs_time,DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%S'))  between 24+1  and 24*2)  "+
                    " </when> "+
                    " <when test=\"hours72=='hours72' and hours72!=''\" > "+
                    " AND y.hs_result='阴性' and (TIMESTAMPDIFF(HOUR,hs_time,DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%S'))  between 24*2+1 and 24*3)  "+
                    " </when> "+
                    " <when test=\"within7Days=='within7Days' and within7Days!=''\" > "+
                    " AND y.hs_result='阴性' and (TIMESTAMPDIFF(HOUR,hs_time,DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%S'))  between 24*3+1 and 24*7)  "+
                    " </when> "+
                    " <when test=\"more7Days=='more7Days' and more7Days!=''\" > "+
                    " AND y.hs_result='阴性' and (TIMESTAMPDIFF(HOUR,hs_time,DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%S')) &gt; 24*7)  "+
                    " </when> "+
                    " <when test=\"ym0=='ym0' and ym0!=''\" > "+
                    " AND y.ym_num=0  "+
                    " </when> "+
                    " <when test=\"ym1=='ym1' and ym1!=''\" > "+
                    " AND y.ym_num=1  "+
                    " </when> "+
                    " <when test=\"ym2=='ym2' and ym2!=''\" > "+
                    " AND y.ym_num=2  "+
                    " </when> "+
                    " <when test=\"ym3=='ym3' and ym3!=''\" > "+
                    " AND y.ym_num=3  "+
                    " </when> "+
                    " <when test=\"ymMore3=='ymMore3' and ymMore3!=''\" > "+
                    " and y.ym_num &gt; 3  "+
                    " </when> "+

                    " <when test=\"encryIdentity!=null and encryIdentity!=''\" > "+
                    " AND i.encry_identity = #{encryIdentity}  "+
                    " </when> "+
                    " <when test=\"encryPhone!=null and encryPhone!=''\" > "+
                    " AND i.encry_phone = #{encryPhone}  "+
                    " </when> "+
                    "order by CONVERT( i.p_name USING gbk ) COLLATE gbk_chinese_ci ASC,i.com_id,i.dept_id  ",
            "</script>"}
    )
    public List<YkmSumListRVo> getYkmSumList(Long comId, List<Integer> deptIdsList, String pCode,
                                             String pName, String pullFailed, String other,String red,String yellow,String green,
                                             String positive,String negative,String noNucleic,String hours24,
                                             String hours48,String hours72,String within7Days,String more7Days,
                                             String ym0,String ym1,String ym2,String ym3,String ymMore3,
                                             String encryIdentity, String encryPhone);
}
